SELECT com_base_khlb.LBMC LBMC,com_base_khsx2.SXMC YWQY,com_base_kehu.KHDM KHDM,com_base_kehu.KHMC KHMC,hyfhyds.HYDS HYDS会员单,hyfhyds.FHYDS FHYDS非会员单数, JSZDS.ZDS as ZDSZ总单数,DD.DDS AS HYDZS修正单数,JSZDS.RQ RQ日期,(JSZDS.ZDS-ifnull(DD.DDS,0)) as ZDS总单数,JSZJS.ZJS ZJS总件数,ROUND(ZJS/(JSZDS.ZDS-ifnull(DD.DDS,0)),2) LDL连带率,(ifnull(LDDS.LDDS,0)+ifnull(zjldds.ds,0)) LDDS连带单数,CONCAT(cast((ifnull(LDDS.LDDS,0)+ifnull(zjldds.ds,0))/(JSZDS.ZDS-ifnull(DD.DDS,0))*100 AS DECIMAL(9,2)),'%') as LDDSZB连带单数占比 
-- 总件数zjs:单价>=69  该单销售数量>0
-- 连带率=总件数zjs/（总单数-当日会员多单数）
-- ！ 同一个会员同一天用券或者是半年卡数量为1的，价格大于69的，调增一单连带单数 也就是zjldds.ds
--  计算连带单数 ldds  (ifnull(LDDS.LDDS,0)+ifnull(zjldds.ds,0)) LDDS连带单数=基础连带单数(单价>69数量>0单数)+zjldds(调增加连带单数：会员当天用券或办年卡 且只买了一双单价>69的) 
       

-- -- //    $sqlquery =  SELECT com_base_khlb.LBMC LBMC,com_base_khsx2.SXMC YWQY,com_base_kehu.KHDM KHDM,com_base_kehu.KHMC KHMC, JSZDS.ZDS as ZDSZ,DD.DDS AS HYDZS,JSZDS.RQ RQ,(JSZDS.ZDS-ifnull(DD.DDS,0)) as ZDS,JSZJS.ZJS ZJS,ROUND(ZJS/(JSZDS.ZDS-ifnull(DD.DDS,0)),2) LDL,(LDDS.LDDS+ifnull(zjldds.ds,0)) LDDS,CONCAT(cast((LDDS.LDDS+ifnull(zjldds.ds,0))/(JSZDS.ZDS-ifnull(DD.DDS,0))*100 AS DECIMAL(9,2)),'%') as LDDSZB 
-- -- //    $sqlquery =  SELECT com_base_khlb.LBMC LBMC,com_base_khsx2.SXMC YWQY,com_base_kehu.KHDM KHDM,com_base_kehu.KHMC KHMC, JSZDS.RQ RQ,(JSZDS.ZDS-ifnull(DD.DDS,0)) as ZDS,JSZJS.ZJS ZJS,ROUND(ZJS/(JSZDS.ZDS-ifnull(DD.DDS,0)),2) LDL,(LDDS.LDDS+zjldds.ds) LDDS,CONCAT(cast((LDDS.LDDS)/(JSZDS.ZDS-ifnull(DD.DDS,0))*100 AS DECIMAL(9,2)),'%') as LDDSZB 
-- -- //    $sqlquery =  SELECT com_base_khlb.LBMC LBMC,com_base_khsx2.SXMC YWQY,com_base_kehu.KHDM KHDM,com_base_kehu.KHMC KHMC, JSZDS.RQ RQ,JSZDS.ZDS ZDS,JSZJS.ZJS ZJS,ROUND(ZJS/ZDS,2) LDL,LDDS.LDDS LDDS,CONCAT(cast(LDDS.LDDS/JSZDS.ZDS*100 AS DECIMAL(9,2)),'%') as LDDSZB 
       FROM 
       (SELECT ZD_ID,RQ,COUNT(DJBH) ZDS 
       FROM  
   -- -- //   -- 计算总单数 
       ( 
       SELECT ZD_ID,FROM_UNIXTIME(QRRQ,'%Y-%m-%d') RQ,DJH DJBH,SUM(SL) SL  
       FROM IPOS_QTLSDMX  
       where qrrq>=UNIX_TIMESTAMP('2026-05-21')  and qrrq<=UNIX_TIMESTAMP('2026-05-21') 
       AND DJ>=69 
       AND ZF=0 AND QR=1 
       GROUP BY ZD_ID,FROM_UNIXTIME(QRRQ,'%Y-%m-%d'),DJH 
    -- //   -- 去掉负数量小票单 
       HAVING SUM(SL)>0 
       ) AS YUAN1 
       GROUP BY ZD_ID,RQ 
       ) AS JSZDS 

    -- //   -- 第一： 总单数减少：计算同一天同一会员多单减1
       left join ( 
       select zd_id,rq,sum(ifnull(dds,0)) dds from ( 
       select  ZD_ID,FROM_UNIXTIME(QRRQ,'%Y-%m-%d') RQ,vpdm,(count(DISTINCT djh)-1) AS dds 
       from ipos_qtlsdmx 
       where qrrq>=UNIX_TIMESTAMP('2026-05-21')  and qrrq<=UNIX_TIMESTAMP('2026-05-21') 
        AND DJ>=69  AND ZF=0 AND QR=1 and ifnull(vpdm,'') !='' and sl>0  

    -- //增加使用年卡或办年条件限制
       and djh in (  
       select distinct djbh from (  
       select djbh from ipos_qtlsd where bz like '券NK%' and yyrq>=UNIX_TIMESTAMP('2026-05-21') and yyrq<=UNIX_TIMESTAMP('2026-05-21') 
       union ALL  
       select djh as  djbh from ipos_qtlsdmx where spdm like 'NK%' and  qrrq>=UNIX_TIMESTAMP('2026-05-21') and qrrq<=UNIX_TIMESTAMP('2026-05-21')  
       ) as dj )

       GROUP BY ZD_ID,FROM_UNIXTIME(QRRQ,'%Y-%m-%d'),vpdm HAVING (count(DISTINCT djh)-1)>0 
       ) as dd1 GROUP BY zd_id,rq 
       ) as dd on dd.zd_id=JSZDS.zd_id and dd.rq=JSZDS.rq 

-- //    第二： 连带单数增加 ：同一会员同一天 多单（数量大于1） 且>69数量为1 （原连带单数+上面会员数）
       left join ( 
       select zd_id,rq,ifnull(count(distinct vpdm),0) ds from ( 
       select  ZD_ID,FROM_UNIXTIME(QRRQ,'%Y-%m-%d') RQ,vpdm,(count(DISTINCT djh)) AS dds 
       from ipos_qtlsdmx 
       where qrrq>=UNIX_TIMESTAMP('2026-05-21')  and qrrq<=UNIX_TIMESTAMP('2026-05-21') 
       AND DJ>=69  AND ZF=0 AND QR=1 and ifnull(vpdm,'') !='' and sl=1 

    -- //增加使用年卡或办年条件限制
       and djh in (  
       select distinct djbh from (  
       select djbh from ipos_qtlsd where bz like '券NK%' and yyrq>=UNIX_TIMESTAMP('2026-05-21') and yyrq<=UNIX_TIMESTAMP('2026-05-21') 
       union ALL  
       select djh as  djbh from ipos_qtlsdmx where spdm like 'NK%' and  qrrq>=UNIX_TIMESTAMP('2026-05-21') and qrrq<=UNIX_TIMESTAMP('2026-05-21')  
       ) as dj )

       GROUP BY ZD_ID,FROM_UNIXTIME(QRRQ,'%Y-%m-%d'),vpdm HAVING (count(DISTINCT djh))>1 
       ) as zjldds1 
       where zd_id='56535'
       GROUP BY zd_id,rq 
       ) as zjldds on zjldds.zd_id=JSZDS.zd_id and zjldds.rq=JSZDS.rq 

-- //    第三 -- 会员单数 非会员单数 20250528
       left join ( 
       select ZD_ID,RQ,count(case when IFNULL(vpdm,'') !='' THEN djh end) as  HYDS,count(case when (IFNULL(vpdm,'') ='' or vpdm='') THEN djh end) as  FHYDS 
       from( 
       select DISTINCT vpdm,djh,ZD_ID,FROM_UNIXTIME(QRRQ,'%Y-%m-%d') RQ  
       from ipos_qtlsdmx 
       where qrrq>=UNIX_TIMESTAMP('2026-05-21')  and qrrq<=UNIX_TIMESTAMP('2026-05-21') 
       AND DJ>=69  AND ZF=0 AND QR=1  and sl>0 
-- //    and zd_id='1029'
       ) as hyfhyds1 
          GROUP BY ZD_ID,rq 
         ) as hyfhyds on hyfhyds.zd_id=JSZDS.zd_id and hyfhyds.rq=JSZDS.rq 

       LEFT JOIN  
       
       -- 连带件数单价>=69  该单销售数量>0
       (SELECT ZD_ID,RQ,SUM(SL) ZJS 
       FROM  
    -- //   -- 计算总件数
       ( 
       SELECT ZD_ID,FROM_UNIXTIME(QRRQ,'%Y-%m-%d') RQ,DJH DJBH,SUM(SL) SL  
       FROM IPOS_QTLSDMX  
       where qrrq>=UNIX_TIMESTAMP('2026-05-21')  and qrrq<=UNIX_TIMESTAMP('2026-05-21') 
       AND DJ>=69 
       AND ZF=0 AND QR=1 
       GROUP BY ZD_ID,FROM_UNIXTIME(QRRQ,'%Y-%m-%d'),DJH 
    -- //   -- 去掉负数量小票单 
       HAVING SUM(SL)>0 
       ) AS YUAN1 
       GROUP BY ZD_ID,RQ) AS JSZJS 
       ON JSZDS.ZD_ID=JSZJS.ZD_ID AND JSZDS.RQ=JSZJS.RQ  
       LEFT JOIN 
       
       --  计算连带单数 ldds
       
       (
       SELECT ZD_ID,RQ,COUNT(DJBH) LDDS 
       FROM  
    -- //   -- 计算总单数 
       ( 
       SELECT ZD_ID,FROM_UNIXTIME(QRRQ,'%Y-%m-%d') RQ,DJH DJBH,SUM(SL) SL  
       FROM IPOS_QTLSDMX  
       where qrrq>=UNIX_TIMESTAMP('2026-05-21')  and qrrq<=UNIX_TIMESTAMP('2026-05-21') 
       AND DJ>=69 
       AND ZF=0 AND QR=1 
       GROUP BY ZD_ID,FROM_UNIXTIME(QRRQ,'%Y-%m-%d'),DJH 
    -- //   -- 去掉负数量小票单 
       HAVING SUM(SL)>=0 
       ) AS YUAN1 
       where zd_id='56535'
       GROUP BY ZD_ID,RQ 
       
       ) AS LDDS 
       ON LDDS.ZD_ID=JSZDS.ZD_ID AND LDDS.RQ=JSZDS.RQ  
    -- //   -- 匹配商店 
       inner JOIN com_base_kehu ON com_base_kehu.ID=JSZDS.ZD_ID  
      
      
       AND com_base_kehu.id='56535'
       
       
       
    -- //   -- 区域业务区域 
       LEFT JOIN com_base_khsx2 ON com_base_khsx2.ID=com_base_kehu.SX2_ID 
       LEFT JOIN com_base_khlb ON com_base_khlb.ID=com_base_kehu.LB_ID